/********************************************************************************************************

Script:			Existing server Schedule update
Author:			Richard Slim
Date:			07/11/2012
Description:	This script will update the schedules for all of the jobs created by the deploy script 
				The configuration section must be set with your preferences or can be run with the defaults
				which are:

				Differential Backup is 12 hours after the full backup
				Log backups run every 15 minutes in prod and every 6 hours in the other environments
				Full System backups 2 hours after the full backup
				Index optimize runs 4 hours after the full backup on Saturdays
				Integrity checks for user databases 6 hours after the full backups on Sundays
				Sys database inegrity checks 2 hours after that
				Cleanup Jobs 4 hours after the full backups on Sundays

Usage:			This is called from within the main SSIS task but can also be used for manual schedule
				changes be replacing the two ?'s with appropriate values.
				
********************************************************************************************************/

--Declare the variables for the script
DECLARE @DiffTime int
DECLARE @FullTime int
DECLARE @IndexTime int
DECLARE @MaintTime int
DECLARE @SysFullTime int
DECLARE @SysMaintTime int
DECLARE @CleanUpTime int
DECLARE @DiffOffset int
DECLARE @LogInterval int
DECLARE @LogIntervalType CHAR(1)
DECLARE @LogIntervalTypeVal int
DECLARE @SysFullOffset int
DECLARE @IndexMaintOffset int
DECLARE @IndexMaintDay int
DECLARE @MaintOffset int
DECLARE @MaintDay int
DECLARE @CleanupOffset int
DECLARE @CleanupDay int
DECLARE @sysMaintOffset int
DECLARE @Environment varchar(5)
DECLARE @Time smalldatetime
SET @Environment = ?
SET @TIME = ?

/********************************************************************************************************/
/************************* SET THE BELOW VALUES FOR YOUR DIFFERENT ENVIRONMENTS *************************/
/********************************************************************************************************/

/**************** How many hours you want your differential after the full backup (Hours) ***************/
IF @Environment = 'Prod'
SET @DiffOffset = 12		--Prod
ELSE IF @Environment = 'Dev'
SET @DiffOffset = 12		--Development
ELSE IF @Environment = 'Trn'
SET @DiffOffset = 12		--Training
ELSE IF @Environment = 'Sit'
SET @DiffOffset = 12		--Sit
ELSE IF @Environment = 'Uat'
SET @DiffOffset = 12		--UAT
/********************************************************************************************************/

/***************** How long in between log backups M = Minute, H = Hour**********************************/
IF @Environment = 'Prod'
BEGIN
	SET @LogInterval = 15			--Prod
	SET @LogIntervalType = 'M'
END
ELSE IF @Environment = 'Dev'
BEGIN
	SET @LogInterval = 6			--Development
	SET @LogIntervalType = 'H'
END
ELSE IF @Environment = 'Trn'
BEGIN
	SET @LogInterval = 6			--Training
	SET @LogIntervalType = 'H'
END
ELSE IF @Environment = 'Sit'
BEGIN
	SET @LogInterval = 6			--Sit
	SET @LogIntervalType = 'H'
END
ELSE IF @Environment = 'Uat'
BEGIN
	SET @LogInterval = 6			--UAT
	SET @LogIntervalType = 'H'
END
/********************************************************************************************************/

/***** How long after the full backup do you want the system databases to be backed up (Hours) **********/
IF @Environment = 'Prod'
SET @SysFullOffset = 2		--Prod
ELSE IF @Environment = 'Dev'
SET @SysFullOffset = 2		--Development
ELSE IF @Environment = 'Trn'
SET @SysFullOffset = 2		--Training
ELSE IF @Environment = 'Sit'
SET @SysFullOffset = 2		--Sit
ELSE IF @Environment = 'Uat'
SET @SysFullOffset = 2		--UAT
/********************************************************************************************************/

/* The offset from the full backup and day of job that runs the index maintenance (Offset = Hours)
1 = Sunday, 2 = Monday, 4 = Tuesday, 8 = Wednesday, 16 = Thursday, 32 = Friday, 64 = Saturday */
IF @Environment = 'Prod'
BEGIN
	SET @IndexMaintOffset = 4			--Prod
	SET @IndexMaintDay = 64				--Prod
END
ELSE IF @Environment = 'Dev'
BEGIN
	SET @IndexMaintOffset = 4			--Development
	SET @IndexMaintDay = 64				--Development
END
ELSE IF @Environment = 'Trn'
BEGIN
	SET @IndexMaintOffset = 4			--Training
	SET @IndexMaintDay = 64				--Training
END
ELSE IF @Environment = 'Sit'
BEGIN
	SET @IndexMaintOffset = 4			--Sit
	SET @IndexMaintDay = 64				--Sit
END
ELSE IF @Environment = 'Uat'
BEGIN
	SET @IndexMaintOffset = 4			--UAT
	SET @IndexMaintDay = 64				--UAT
END
/********************************************************************************************************/

/*		The offset from the full backup and day of job that runs the integrity checks (Offset = Hours)
1 = Sunday, 2 = Monday, 4 = Tuesday, 8 = Wednesday, 16 = Thursday, 32 = Friday, 64 = Saturday */
IF @Environment = 'Prod'
BEGIN
	SET @MaintOffset = 6			--Prod
	SET @MaintDay = 1				--Prod
END
ELSE IF @Environment = 'Dev'
BEGIN
	SET @MaintOffset = 6			--Development
	SET @MaintDay = 1				--Development
END
ELSE IF @Environment = 'Trn'
BEGIN
	SET @MaintOffset = 6			--Training
	SET @MaintDay = 1				--Training
END
ELSE IF @Environment = 'Sit'
BEGIN
	SET @MaintOffset = 6			--Sit
	SET @MaintDay = 1				--Sit
END
ELSE IF @Environment = 'Uat'
BEGIN
	SET @MaintOffset = 6			--UAT
	SET @MaintDay = 1				--UAT
END
/********************************************************************************************************/

/***** How long after the integrity checks do you want the system databases to be checked (Hours) *******/
SET @sysMaintOffset = 2
/********************************************************************************************************/

/*		The offset from the full backup and day of job that runs the cleanup jobs run (Offset = Hours)
1 = Sunday, 2 = Monday, 4 = Tuesday, 8 = Wednesday, 16 = Thursday, 32 = Friday, 64 = Saturday */

IF @Environment = 'Prod'
BEGIN
	SET @CleanupOffset = 4			--Prod
	SET @CleanupDay = 1				--Prod
END
ELSE IF @Environment = 'Dev'
BEGIN
	SET @CleanupOffset = 4			--Development
	SET @CleanupDay = 1				--Development
END
ELSE IF @Environment = 'Trn'
BEGIN
	SET @CleanupOffset = 4			--Training
	SET @CleanupDay = 1				--Training
END
ELSE IF @Environment = 'Sit'
BEGIN
	SET @CleanupOffset = 4			--Sit
	SET @CleanupDay = 1				--Sit
END
ELSE IF @Environment = 'Uat'
BEGIN
	SET @CleanupOffset = 4			--UAT
	SET @CleanupDay = 1				--UAT
END
/********************************************************************************************************/

/********************************************************************************************************/
/******************* END OF CONFIGURATION, DON'T TOUCH THE BELOW UNLESS YOU WANT TO *********************/
/********************************************************************************************************/

--Set schedule for DatabaseBackup - USER_DATABASES - FULL

SET @FullTime = CAST(REPLACE(RIGHT(CONVERT(VARCHAR(20),@time,126),8),':','') as int)

EXEC msdb.dbo.sp_update_schedule
    @name = N'FullBackup',
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = @FullTime

--Set schedule for DatabaseBackup - USER_DATABASES - DIFF

SET @DiffTime = CAST(REPLACE(RIGHT(CONVERT(VARCHAR(20),DATEADD(hh,@DiffOffset,@Time),126),8),':','') as int)

EXEC msdb.dbo.sp_update_schedule
    @name = N'DiffBackup',
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = @DiffTime

--Set schedule for DatabaseBackup - USER_DATABASES - LOG

IF @LogIntervalType = 'M'
	SET @LogIntervalTypeVal = 4
ELSE IF @LogIntervalType = 'H'
	SET @LogIntervalTypeVal = 8

EXEC msdb..sp_update_schedule
    @name = N'LogBackup',
	@freq_type  = 4,
    @freq_interval = 1,
    @freq_subday_type  = @LogIntervalTypeVal,
    @freq_subday_interval = @LogInterval

--Set schedule for DatabaseBackup - SYSTEM_DATABASES - FULL

SET @SysFullTime = CAST(REPLACE(RIGHT(CONVERT(VARCHAR(20),DATEADD(hh,@SysFullOffset,@Time),126),8),':','') as int)

EXEC msdb..sp_update_schedule
    @name = N'SysFullBackup',
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = @SysFullTime

--Set Schedule for IndexOptimize - USER_DATABASES

SET @IndexTime = CAST(REPLACE(RIGHT(CONVERT(VARCHAR(20),DATEADD(hh,@IndexMaintOffset,@Time),126),8),':','') as int)

EXEC msdb..sp_update_schedule
    @name = N'IndexOptimize',
    @freq_type = 8,
    @freq_interval = @IndexMaintDay,
	@freq_recurrence_factor = 1,
    @active_start_time = @IndexTime

--Set Schedule for DatabaseIntegrityCheck - USER_DATABASES

SET @MaintTime = CAST(REPLACE(RIGHT(CONVERT(VARCHAR(20),DATEADD(hh,@MaintOffset,@Time),126),8),':','') as int)

EXEC msdb..sp_update_schedule
    @name = N'DBIntegrity',
    @freq_type = 8,
    @freq_interval = @MaintDay,
	@freq_recurrence_factor = 1,
    @active_start_time = @MaintTime

--Set Schedule for DatabaseIntegrityCheck - SYSTEM_DATABASES

DECLARE @SysMaintTime_TEMP smalldatetime 
SET @SysMaintTime_TEMP = DATEADD(hh,@MaintOffset,@Time)
SET @SysMaintTime = CAST(REPLACE(RIGHT(CONVERT(VARCHAR(20),DATEADD(hh,@sysMaintOffset,@SysMaintTime_TEMP),126),8),':','') as int)

EXEC msdb..sp_update_schedule
    @name = N'DBSysIntegrity',
    @freq_type = 8,
    @freq_interval = @MaintDay,
	@freq_recurrence_factor = 1,
    @active_start_time = @SysMaintTime

--Set Schedule for Output File Cleanup and CommandLog Cleanup

SET @CleanUpTime = CAST(REPLACE(RIGHT(CONVERT(VARCHAR(20),DATEADD(hh,@CleanupOffset,@Time),126),8),':','') as int)

EXEC msdb..sp_update_schedule
    @name = N'Cleanups',
    @freq_type = 8,
    @freq_interval = @CleanupDay,
	@freq_recurrence_factor = 1,
    @active_start_time = @CleanUpTime